package student.aistar.util.reflect;


import student.aistar.model.pojo.User;
import student.aistar.model.pojo.Video;
import student.aistar.util.JdbcUtil;

import java.lang.reflect.InvocationTargetException;
import java.util.Date;
import java.io.Serializable;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 反射封装jdbc工具类
 */
public class JdbcNBUtil {
    public static <T> T findById(Class<?> c, Serializable id){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        String className = c.getSimpleName();
        StringBuilder sqlBuilder = new StringBuilder();
        T t = null;
        try {
            connection = JdbcUtil.getConnection();
            sqlBuilder.append("select * from " + className + " where id=" + id);
            preparedStatement = connection.prepareStatement(sqlBuilder.toString());
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                t = (T) c.newInstance();
                ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
                int cols = resultSetMetaData.getColumnCount();
                for (int i = 1; i < cols; i++) {
                    String colName = resultSetMetaData.getColumnName(i);
                    colName = changeColName(colName);
                    Field f = c.getDeclaredField(colName);
                    f.setAccessible(true);
                    if("DATETIME".equals(resultSetMetaData.getColumnTypeName(i))){
                        f.set(t, resultSet.getTimestamp(i));
                    }else
                        f.set(t, resultSet.getObject(i));

                }
            }
        } catch (Exception throwables) {
            throwables.printStackTrace();
        }
        return t;
    }

    public static <T> T queryOne(Class<?> c, String sql, Object... args){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        T t = null;
        try {
            connection = JdbcUtil.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }
            resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
//                Field[] fields = c.getDeclaredFields();
//                t = (T) c.newInstance();
//
//                for(int i = 0; i < fields.length; i++){
//                   fields[i].setAccessible(true);
//                   fields[i].set(t, resultSet.getObject(i + 1));
//                }
                ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
                int cols = resultSetMetaData.getColumnCount();
                t = (T) c.newInstance();
                for (int i = 1; i <= cols; i++) {
                    Field field = c.getDeclaredField(resultSetMetaData.getColumnName(i));

                    field.setAccessible(true);
                    field.set(t, resultSet.getObject(i));
                }
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        }
        return t;
    }

    public static int delById(Class<?> c, Serializable id){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JdbcUtil.getConnection();
            String sql = "delete from t_" + c.getSimpleName().toLowerCase() + " where id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setObject(1, id);
            return preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;

    }

    public static <T> T getById(Class<?> c, Serializable id){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        T t = null;
        try {
            connection = JdbcUtil.getConnection();
            String className = c.getSimpleName().toLowerCase();
            String sql = "select * from t_" + className + " where id=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setObject(1, id);
            resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                t = (T) c.newInstance();
                ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
                int cols = resultSetMetaData.getColumnCount();
                for (int i = 1; i <= cols; i++) {
                    String colName =resultSetMetaData.getColumnName(i);
                    colName = changeColName(colName);
                    String propName = "set" +String.valueOf(colName.charAt(0)).toUpperCase() + colName.substring(1);
                    String type = resultSetMetaData.getColumnTypeName(i);
                    Method method = null;
                    if("INT".equals(type))
                        method = c.getMethod(propName, Integer.class);
                    else if("VARCHAR".equals(type))
                        method = c.getMethod(propName, String.class);
                    else if("DATE".equals(type) || "DATETIME".equals(type))
                        method = c.getMethod(propName, Date.class);
                    else if("DOUBLE".equals(type))
                        method = c.getMethod(propName, Double.class);
                    if("DATETIME".equals(type))
                        method.invoke(t, resultSet.getTimestamp(i));
                    else
                        method.invoke(t, resultSet.getObject(i));
                }
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        }
        return t;
    }

    /**
     * ORM - Hibernate
     * Session session = factory.getSession();
     * Transaction tx = session.beginTransaction();
     *
     * //看不到sql语句的 - api方法
     * session.save(user)
     *
     * tx.commit();
     * session.close();
     * @param object
     * @return
     */
    public static int save(Object object){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        //考虑sql语句 - 规则的
        Class<?> c = object.getClass();//反射技术的基础类
        String className = c.getSimpleName();
        //规则 - 表明应该类名保持一致, 或者有一个统一的前缀
        StringBuilder sqlBuilder = new StringBuilder();
        //insert into 表明 values(?,?,?);
        sqlBuilder.append("insert into t_" + className.toLowerCase() + " values(");
        //占位符号应该和实体类的属性的个数保持一致
        Field[] field = c.getDeclaredFields();
        int fieldCounts = field.length;
        for (int i = 0; i < fieldCounts; i++) {
            if(i != fieldCounts - 1){
                sqlBuilder.append("?,");
            }else
                sqlBuilder.append("?)");
        }
//        System.out.println(sqlBuilder);
        try {
            connection = JdbcUtil.getConnection();
            preparedStatement = connection.prepareStatement(sqlBuilder.toString());
            for (int i = 0; i < fieldCounts; i++) {
                field[i].setAccessible(true);
                preparedStatement.setObject(i + 1, field[i].get(object));
            }
            return preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }finally {
            JdbcUtil.close(connection, preparedStatement);
        }
        return 0;
    }

    //通用的单表DML操作[inert, update, delete]
    public static int update(String sql, Object... args){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        int count = 0;
        try {
            connection = JdbcUtil.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            if(null != args && args.length > 0){
                for (int i = 0; i < args.length; i++) {
                    preparedStatement.setObject(i + 1, args[i]);
                }
            }
            count = preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return count;
    }

    //通用的万能查询, 返回的是多个对象
    //能够实现自动封装, 表查询结果 - 对象中
    /**
     * @param c 目标对象的class实例
     * @param sql   带有占位符的sql语句
     * @param args  参数
     * @param <T>
     * @return
     */
    public static <T> List<T> queryList(Class<?> c, String sql, Object... args){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<T> dataList = new ArrayList<>();

        T t = null;
        try {
            connection = JdbcUtil.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            //考虑要不要发送参数呢
            if(null != args && args.length > 0){
                for (int i = 0; i < args.length; i++) {
                    //设置参数
                    //健壮性 - 此处还需要对部分类型进行处理
                    preparedStatement.setObject(i+1, args[i]);
                }
            }
            //拿到结果集对象
            resultSet = preparedStatement.executeQuery();
            //结果集元数据
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            //获取列的个数, 获取列的名称
            int cols = resultSetMetaData.getColumnCount();
            while (resultSet.next()){
                t = (T) c.newInstance();
                //反射设置值 - 属性名 - Field对象
                for (int i = 1; i <= cols; i++) {
                    String colName = resultSetMetaData.getColumnName(i);
//                    System.out.println(colName);
                    colName = changeColName(colName);//防止出现了匈牙利 -> 小驼峰
                    Field f = c.getDeclaredField(colName);
                    f.setAccessible(true);
                    //需要对类型进行很多判断..
                    //对日期类型进行一个单独的处理
                    String colType = resultSetMetaData.getColumnTypeName(i);
                    //System.out.println(colType);//列的属性
                    if("DATETIME".equals(colType)){
                        f.set(t, resultSet.getTimestamp(i));
                    }else
                        f.set(t, resultSet.getObject(i));
                }
                dataList.add(t);
            }
        } catch (Exception throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                JdbcUtil.close(connection,preparedStatement,resultSet);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return dataList;
    }

    private static String changeColName(String colName) {
        StringBuilder builder = new StringBuilder();
        //判断是不是采用的匈牙利的命名方式
        if(colName.contains("_")){
            //根据_切割
            String[] arr = colName.split("_");
            builder.append(arr[0]);

            //从数组中的第二个位置开始
            for (int i = 1; i < arr.length; i++) {
                builder.append(String.valueOf(arr[i].charAt(0)).toUpperCase()).append(arr[i].substring(1));
            }
        }else
            return colName;
        return builder.toString();
    }

    public static void main(String[] args) {
//        List<Video> videos = queryList(Video.class,"select * from t_video where id=?",2);
////        System.out.println(changeColName(""));
//
//        for (Video video : videos) {
//            System.out.println(video);
//        }

//        update("insert into t_video values(?,?,?,?,?,?)",
//                null,"反射3",new java.util.Date(),900.0d,"http://baidu.com",4);

//        Video v = findById(Video.class, 2);
//        System.out.println(v);
//        Video v= new Video(null,"反射save",new java.util.Date(),800.0d,"http://taobao",4);
//        System.out.println(save(v));

//        Video v = getById(Video.class, 2);
//        System.out.println(v);

//        System.out.println(delById(Video.class, 1224));

//        User user = queryOne(User.class, "select * from user where id=?", 2);
//        System.out.println(user);
    }
}
